package org.madmac.maquina;

import org.madmac.pessoa.PessoaQuery;
import org.madmac.setor.SetorQuery;
import org.wakeful.conn.Connection;
import org.wakeful.query.Query;

/**
 *
 * @author atila camurca
 */
public final class MaquinaQuery {

   public static final String TABLE = Connection.getSchema().concat(Query.DOT).concat("maquina");
   public static final String ALIAS = TABLE.concat(" ma");

   private MaquinaQuery() {}

   public static String create() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.INSERT);
      builder.append(MaquinaQuery.TABLE);
      builder.append(" (id_setor, tombamento, id_pessoa, sistema_op, nome_dominio)");
      builder.append(Query.VALUES);
      builder.append("(?, ?, ?, ?, ?)");
		builder.append(Query.RETURNING);
		builder.append("id_maquina");
      return builder.toString();
   }

	public static String update() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.UPDATE);
      builder.append(MaquinaQuery.TABLE);
      builder.append(Query.SET);
      builder.append("id_setor = ?, ");
      builder.append("id_pessoa = ?, ");
      builder.append("sistema_op = ?, ");
      builder.append("nome_dominio = ?");
      builder.append(Query.WHERE);
      builder.append("tombamento = ?");
      return builder.toString();
   }

	public static String retrieveIdMaquina() {
		StringBuilder builder = new StringBuilder();
		builder.append(Query.SELECT);
		builder.append("id_maquina");
		builder.append(Query.FROM);
		builder.append(MaquinaQuery.TABLE);
		builder.append(Query.WHERE);
		builder.append("tombamento = ?");
		return builder.toString();
	}

	public static String findByTombamento() {
		StringBuilder builder = new StringBuilder();
		builder.append(Query.SELECT);
		builder.append("ma.id_maquina, ma.id_setor, ma.tombamento, ma.id_pessoa, ");
		builder.append("p.nome AS responsavel, ma.sistema_op, s.nome AS setor, ma.nome_dominio");
		builder.append(Query.FROM);
		builder.append(MaquinaQuery.ALIAS);
		builder.append(Query.LEFT_JOIN);
		builder.append(SetorQuery.ALIAS);
		builder.append(Query.ON);
		builder.append("ma.id_setor = s.id_setor");
		builder.append(Query.LEFT_JOIN);
		builder.append(PessoaQuery.ALIAS);
		builder.append(Query.ON);
		builder.append("ma.id_pessoa = p.id_pessoa");
		builder.append(Query.WHERE);
		builder.append("ma.tombamento = ?");
		builder.append(Query.ORDER_BY);
		builder.append("s.nome");
		return builder.toString();
	}

	public static String findAllByIdSetor() {
		StringBuilder builder = new StringBuilder();
		builder.append(Query.SELECT);
		builder.append("ma.id_maquina, ma.tombamento, ma.id_setor, ma.id_pessoa, ");
		builder.append("ma.sistema_op, s.nome AS setor, hd.caminho, p.nome AS responsavel, ma.nome_dominio");
		builder.append(Query.FROM);
		builder.append(MaquinaQuery.ALIAS);
		builder.append(Query.LEFT_JOIN);
		builder.append(SetorQuery.ALIAS);
		builder.append(Query.ON);
		builder.append("ma.id_setor = s.id_setor");
		builder.append(Query.LEFT_JOIN);
		builder.append(PessoaQuery.ALIAS);
		builder.append(Query.ON);
		builder.append("ma.id_pessoa = p.id_pessoa");
		builder.append(Query.LEFT_JOIN);
		builder.append("madmac.host_details hd");
		builder.append(Query.ON);
		builder.append("hd.hostnumber = ma.tombamento");
		builder.append(Query.WHERE);
		builder.append("ma.id_setor = ?");
		builder.append(Query.ORDER_BY);
		builder.append("s.nome, ma.tombamento");
		return builder.toString();
	}
}
